/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Types.AccountType;
import java.sql.*;

/**
 * Contains the functions that contains the sql queries that can manipulate and access the database
 * @author Jason and Sam
 */
public class MySQLAccountDAO implements AccountDAO {

    private ResultSet rs;
    private Statement stmt;

    public MySQLAccountDAO() {
        //initialization
    }

    /**
     * Accepts a uname and a pass, checks the db for them. If a record is found its data is added
     * to an AccountType and that AccountType is returned
     * @param uname
     * @param pass
     * @return AccountType
     */
    public AccountType validAccount(String uname, String pass) {
        String acc_id = null, acc_type = null, usname = null, pword = null, fname = null, sname = null, address = null, btype = null, bdetails = null;
        AccountType account = new AccountType();
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM ACCOUNT " +
                    "WHERE USERNAME = '" + uname + "' AND PASSWORD = '" + pass + "'");
            while (rs.next()) {
                acc_id = rs.getString("ACCOUNT_ID");
                acc_type = rs.getString("ACCOUNT_TYPE");
                usname = rs.getString("USERNAME");
                pword = rs.getString("PASSWORD");
                fname = rs.getString("FNAME");
                sname = rs.getString("SNAME");
                address = rs.getString("ADDRESS");
                btype = rs.getString("BILLING_TYPE");
                bdetails = rs.getString("BILLING_DETAILS");
            }

            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
        if (acc_id != null) {
            account.setPassword(pword);
            account.setUsername(usname);
            account.setFirstName(fname);
            account.setLastName(sname);
            account.setAddress(address);
        } else {
            return null;
        }
        return account;
    }

    /**
     * Accepts all the users details and adds them to the db
     * @param uname
     * @param pass
     * @param fname
     * @param sname
     * @param address
     */
    public void registerUser(String uname, String pass, String fname, String sname, String address) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            stmt.executeUpdate("INSERT INTO ACCOUNT ( `ACCOUNT_TYPE` , `USERNAME` , `PASSWORD` , `FNAME` , `SNAME` , `ADDRESS` ) " +
                    "VALUES ( 'USER', " + "'" + uname + "', " + "'" + pass + "', " + "'" + fname + "', " + "'" + sname + "', " + "'" + address + "' )");
            System.out.println("User Registered");
            con.close();
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
        }
    }

    /**
     * Will delete a record that is specified by uname
     * @param uname
     * @return boolean
     */
    public boolean deleteAccount(String uname) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            stmt.executeUpdate("DELETE FROM ACCOUNT " +
                    "WHERE USERNAME = '" + uname + "';");
            System.out.println("User Deleted");
            con.close();
            return true;
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
            return false;
        }
    }

    /**
     * Will try to find a record specified by uname and return true if none is found
     * Meaning a user can be added with that name
     * @param uname
     * @return boolean
     */
    public boolean checkAvailability(String uname) {
        Connection con = MySQLDAOFactory.createConnection();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT `ACCOUNT_ID` FROM ACCOUNT " +
                    "WHERE USERNAME = '" + uname + "'");

            if (rs.next()) {
                con.close();
                return false;
            } else {
                con.close();
                return true;
            }
        } catch (Exception e) {
            System.out.println("Error message " + e.getMessage());
            return false;
        }
    }
}
